package com.cpi.cornpp.instructions.futures.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.instructions.futures.dto.InstructionsFuturesDTO;
import com.cpi.cornpp.instructions.futures.exception.InstructionsFuturesException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Instructions Futures Data Access
 * @author Edgar Joao
 */
public class InstructionsFuturesDAO extends DBDAO {	
	private static final String SELECT_INSTRUCTIONS_FUTURES_BY_ID = "SELECT purchase_instruction_future_id,purchase_instruction_detail_id,application_future_period_id,account_future_period_id,price,minior_price,stop_loss,quantity,(select sum(quantity) from dbo.realizeds_instructions ri inner join dbo.corn_futures cf on ri.corn_future_id = cf.corn_future_id where ri.purchase_instruction_future_id = purchases_instructions_futures.purchase_instruction_future_id) FROM purchases_instructions_futures WHERE purchase_instruction_detail_id = ?";//$NON-NLS-1$
	private static final String INSERT_SELECT_INSTRUCTIONS_FUTURES = "INSERT INTO purchases_instructions_futures (quantity,price,account_future_period_id,application_future_period_id,minior_price,stop_loss,purchase_instruction_detail_id) VALUES(?,?,?,?,?,?,?)";//$NON-NLS-1$
	private static final String UPDATE_SELECT_INSTRUCTIONS_FUTURES = "UPDATE purchases_instructions_futures SET quantity = ?, price = ?, account_future_period_id = ?, application_future_period_id = ?, minior_price = ?, stop_loss = ? WHERE purchase_instruction_future_id = ?";//$NON-NLS-1$
	private static final String DELETE_SELECT_INSTRUCTIONS_FUTURES = "DELETE FROM purchases_instructions_futures WHERE purchase_instruction_future_id = ?";//$NON-NLS-1$
	private static final String SELECT_BALANCE_FUTURES = "select	sum( restante ) from (SELECT quantity-(select isnull(sum(quantity),0) from dbo.realizeds_instructions ri inner join dbo.corn_futures cf on ri.corn_future_id = cf.corn_future_id where ri.purchase_instruction_future_id = purchases_instructions_futures.purchase_instruction_future_id) as restante FROM purchases_instructions_futures WHERE purchase_instruction_detail_id = ? ) as dummy";
	/**
	 * @author Edgar Joao
	 * @param id
	 * @return
	 * @throws InstructionsFuturesException
	 */
	public List getInstructionsFuturesList(int id) throws InstructionsFuturesException {
		List list = null;
		PreparedStatement preparedStatement = null;
		InstructionsFuturesDTO instructionsFuturesDTO = null;
		ResultSet rs = null;
		try{				
			list = new ArrayList(0);			
			preparedStatement = prepareSQL(SELECT_INSTRUCTIONS_FUTURES_BY_ID, dsName);
			preparedStatement.setInt(1, id);	
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				instructionsFuturesDTO = new InstructionsFuturesDTO();
				instructionsFuturesDTO.setPurchaseInstructionFutureId(rs.getInt(1));
				instructionsFuturesDTO.setPurchaseInstructionDetailId(rs.getInt(2));
				instructionsFuturesDTO.setApplicationFuturePeriodId(rs.getInt(3));
				instructionsFuturesDTO.setAccountFuturePeriodId(rs.getInt(4));
				instructionsFuturesDTO.setPrice(rs.getDouble(5));
				instructionsFuturesDTO.setMiniorPrice(rs.getInt(6));
				instructionsFuturesDTO.setStopLoss(rs.getDouble(7));
				instructionsFuturesDTO.setQuantity(rs.getInt(8));
				instructionsFuturesDTO.setBalance(rs.getInt(9));
				list.add(instructionsFuturesDTO);
			}	
		}catch (PoolException poolException){
			InstructionsFuturesException instructionsFuturesException = null;
			poolException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionsFuturesException;	
		} catch (SQLException sqlException) {
			InstructionsFuturesException instructionsFuturesException = null;
			sqlException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionsFuturesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return list;		
	}
	/**
	 * ADD
	 * @author Edgar Joao
	 * @param dto
	 * @throws InstructionsFuturesException
	 */
	public void insertInstructionsFutures(InstructionsFuturesDTO dto) throws InstructionsFuturesException {
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(INSERT_SELECT_INSTRUCTIONS_FUTURES, dsName);							
			preparedStatement.setInt(1, dto.getQuantity());
			preparedStatement.setDouble(2, dto.getPrice());
			preparedStatement.setInt(3, dto.getAccountFuturePeriodId());
			preparedStatement.setInt(4, dto.getApplicationFuturePeriodId());
			preparedStatement.setDouble(5, dto.getMiniorPrice());
			preparedStatement.setDouble(6, dto.getStopLoss());
			preparedStatement.setInt(7, dto.getPurchaseInstructionDetailId());
			preparedStatement.executeUpdate();						
		}catch (PoolException poolException){
			InstructionsFuturesException instructionsFuturesException = null;
			poolException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw instructionsFuturesException;	
		} catch (SQLException sqlException) {
			InstructionsFuturesException instructionsFuturesException = null;
			sqlException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw instructionsFuturesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	/**
	 * EDIT
	 * @author Edgar Joao
	 * @param dto
	 * @throws InstructionsFuturesException
	 */
	public void updateInstructionsFutures(InstructionsFuturesDTO dto) throws InstructionsFuturesException {
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(UPDATE_SELECT_INSTRUCTIONS_FUTURES, dsName);		
			preparedStatement.setInt(1, dto.getQuantity());
			preparedStatement.setDouble(2, dto.getPrice());
			preparedStatement.setInt(3, dto.getAccountFuturePeriodId());
			preparedStatement.setInt(4, dto.getApplicationFuturePeriodId());
			preparedStatement.setDouble(5, dto.getMiniorPrice());
			preparedStatement.setDouble(6, dto.getStopLoss());
			preparedStatement.setInt(7, dto.getPurchaseInstructionFutureId());
			preparedStatement.executeUpdate();
		}catch (PoolException poolException){
			InstructionsFuturesException instructionsFuturesException = null;
			poolException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw instructionsFuturesException;	
		} catch (SQLException sqlException) {
			InstructionsFuturesException instructionsFuturesException = null;
			sqlException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw instructionsFuturesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	/**
	 * DELETE
	 * @param id
	 * @throws InstructionsFuturesException
	 */
	public void deleteInstructionsFutures(int id) throws InstructionsFuturesException {
		PreparedStatement preparedStatement = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			preparedStatement = prepareSQL(DELETE_SELECT_INSTRUCTIONS_FUTURES, dsName);					
			preparedStatement.setInt(1, id);				
			preparedStatement.executeUpdate();			
			executeUpdate(preparedStatement);			
		}catch (PoolException poolException){
			InstructionsFuturesException instructionsFuturesException = null;
			poolException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw instructionsFuturesException;	
		} catch (SQLException sqlException) {
			InstructionsFuturesException instructionsFuturesException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if(sqlException.getErrorCode() == systemParameterBean.getIntValue()){
				instructionsFuturesException = new InstructionsFuturesException(DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}else{
				instructionsFuturesException = new InstructionsFuturesException(sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}
			sqlException.printStackTrace(System.out);
			throw instructionsFuturesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	
	public int instructionFuturesBalance(int purchaseInstructionDetailId)throws InstructionsFuturesException{
		int balance = 0;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			preparedStatement = prepareSQL(SELECT_BALANCE_FUTURES, dsName);
			preparedStatement.setInt(1, purchaseInstructionDetailId);
			resultSet = executeQuery(preparedStatement);
			if( resultSet.next() ){
				balance = resultSet.getInt(1);
			}
		}catch (PoolException poolException){
			InstructionsFuturesException instructionsFuturesException = null;
			poolException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw instructionsFuturesException;	
		} catch (SQLException sqlException) {
			InstructionsFuturesException instructionsFuturesException = null;
			sqlException.printStackTrace(System.out);
			instructionsFuturesException = new InstructionsFuturesException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw instructionsFuturesException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return balance;
	}
}
